﻿using System.Data.SqlClient;
using System.Collections.Generic;
using System;

public class DatabaseManager
{
    private static DatabaseManager instance;

    private SqlConnection conn;

    private DatabaseManager()
    {
        conn = new SqlConnection("Data Source=idasql.ida.utb.hb.se,56001;Initial Catalog=dbtht1034;Persist Security Info=True;User ID=dbtht1034;Password=bluttis09;");
    }

    public static DatabaseManager getInstance()
    {
        if (instance == null)
            instance = new DatabaseManager();
        return instance;
    }

    private SqlDataReader DoSqlQuery(string query)
    {
        SqlDataReader reader = null;
        SqlCommand command;

        try
        {
            command = conn.CreateCommand();
            command.CommandText = query;
            reader = command.ExecuteReader();
        }
        catch (Exception e)
        {
        }

        return reader;
    }

    private int DoSqlNonQuery(string query)
    {
        SqlCommand command;

        try
        {
            command = conn.CreateCommand();
            command.CommandText = query;
            return command.ExecuteNonQuery();
        }
        catch (Exception e)
        {
        }

        return 0;
    }

    public int Size {
        get {

            int count = 0;

            try
            {
                conn.Open();
                SqlDataReader reader = DoSqlQuery("SELECT COUNT(*) FROM Mobil_Musik");
                if (!reader.Read())
                    return 0;
                count = reader.GetInt32(0);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
            
            return count;
        }
    }

    public void Add(Element element, Element parent)
    {
        try
        {
            conn.Open();
            if (parent.Id > 0)
                DoSqlNonQuery(String.Format("INSERT INTO Mobil_Musik (parentID, title, description) VALUES ({0}, '{1}', '{2}')", parent.Id, element.Title, element.Text));
            else
                DoSqlNonQuery(String.Format("INSERT INTO Mobil_Musik (title, description) VALUES ('{0}', '{1}')", element.Title, element.Text));
        }
        catch (Exception e)
        {
        }
        finally
        {
            conn.Close();
        }
    }

    public void Modify(Element element)
    {

        try
        {
            conn.Open();
            DoSqlNonQuery(String.Format("UPDATE Mobil_Musik set title = '{0}', description = '{1}', date = GETDATE() WHERE id = {2}", element.Title, element.Text, element.Id));
        }
        catch (Exception) { }
        finally
        {
            conn.Close();
        }
    }

    public List<Element> GetAll()
    {
        List<Element> artists = GetArtists();
        List<Element> res = new List<Element>();

        foreach (Element a in artists)
        {
            res.Add(a);

            List<Element> albums = GetByParentId(a.Id);

            foreach (Element al in albums)
            {
                res.Add(al);
                res.AddRange(GetByParentId(al.Id));
            }
        }
        
        return res;
    }

    private List<Element> GetArtists()
    {
        return GetByParentId(0);
    }

    public List<Element> GetByParentId(int id)
    {
        List<Element> res = new List<Element>();
        Element e;

        try
        {
            conn.Open();

            string query = String.Format("SELECT * FROM Mobil_Musik WHERE parentID {0} ORDER BY title ASC", (id == 0) ? "IS NULL" : "= "+id.ToString());

            SqlDataReader reader = DoSqlQuery(query);

            while (reader.Read())
            {
                e = new Element();
                e.Id = reader.GetInt32(0);
                e.ParentId = id;
                e.Date = reader.GetDateTime(2);
                e.Title = reader.GetString(3);

                try
                {
                    e.Text = reader.GetString(4);
                }
                catch (Exception ex)
                {
                    e.Text = "";
                }
                finally
                {
                    res.Add(e);
                }
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            conn.Close();
        }

        return res;
    }

    public Element Get(int id)
    {
        Element e = null;

        try
        {
            conn.Open();

            SqlDataReader reader = DoSqlQuery(String.Format("SELECT * FROM Mobil_Musik WHERE id = {0}", id));

            if (!reader.Read())
                return null;

            e = new Element();

            try
            {
                e.ParentId = reader.GetInt32(1);
            }
            catch (Exception ex)
            {
                e.ParentId = 0;
            }

            e.Id = reader.GetInt32(0);
            e.Date = reader.GetDateTime(2);
            e.Title = reader.GetString(3);
            e.Text = reader.GetString(4);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            conn.Close();
        }

        return e;
    }
}